/* *******************************************
  funcion y trigger para verificar cant x lote 

  ale - junio 2020

  ******************************************** */
-- Function: stock.verificar_lote(integer, integer, integer, character varying, timestamp without time zone, character varying)

-- DROP FUNCTION stock.verificar_lote(integer, integer, integer, character varying, timestamp without time zone, character varying);

CREATE OR REPLACE FUNCTION stock.verificar_lote(
    integer,
    integer,
    integer,
    character varying,
    timestamp without time zone,
    character varying)
  RETURNS boolean AS
$BODY$
-- funcion que controla el stock x lote
DECLARE
pIdComm    ALIAS FOR $1; 
pIdArtm    ALIAS FOR $2;
pIdArt     ALIAS FOR $3;
pNroLote   ALIAS FOR $4;
pFechaLote ALIAS FOR $5;
pTG_OP     ALIAS FOR $6;

vSysPrm VARCHAR;
vStrVerificarDesde  VARCHAR;
vDebeVerificarLote BOOLEAN;
vFchVerificarDesde TIMESTAMP;
vTotalLote numeric(16,2);
vErrorOper VARCHAR;
BEGIN
 -- recuperar art.registralote valores del art
  vDebeVerificarLote := COALESCE((SELECT registralote FROM public.art WHERE Id = pIdart), 'N') = 'S';

  -- recuperar sysParm.registralote que puede ser 'N' o 'S' o 'S;fechaInicio'
  IF vDebeVerificarLote THEN
    vSysPrm := Upper((SELECT str_get_atrib_ini(ini, 'ArtModoRegistraLote') FROM ambcfg WHERE id = 1));
    vDebeVerificarLote := ( SELECT (split_part(vSysPrm, ';', 1) = 'S') );
    IF vDebeVerificarLote THEN
      --recuperar inicio "modo lotes"
      vStrVerificarDesde  := (SELECT split_part(vSysPrm, ';', 2) );
      IF (vStrVerificarDesde <> '') THEN
        vFchVerificarDesde := (SELECT vStrVerificarDesde ::date);
        vDebeVerificarLote := ( (SELECT fecha FROM comm WHERE id = pIdcomm) > vFchVerificarDesde );  
      END IF; 
    END IF;
  END IF;

  IF vDebeVerificarLote THEN
    vTotalLote := (SELECT COALESCE(SUM(artm.cant * artm.signo), 0.00)
                   FROM artm 
                   WHERE artm.idart = pidArt AND artm.modificastock = 'S' AND 
                         artm.nroLote = pNroLote AND COALESCE(artm.fechaLote, now()) = COALESCE(pFechaLote, now()) );
    IF vTotalLote < 0 THEN
      IF pTG_OP =    'INSERT' THEN vErrorOper := 'crear';
      ELSIF pTG_OP = 'DELETE' THEN vErrorOper := 'anular/eliminar ';
      ELSE                         vErrorOper := 'modificar';
      END IF;
      RAISE EXCEPTION USING MESSAGE =
                    'No se puede '|| vErrorOper || ' el comprobante porque hay movimientos posteriores sobre el numero/fecha del lote original '||  
                    '(modificar nro o fecha de un lote implica referirse a un lote diferente).'|| 
                    chr(10) ||
                    chr(10) ||'Articulo: '|| (SELECT art.nombre FROM art WHERE art.id = pidArt) ||
                    chr(10) ||'Lote: '|| COALESCE(pNroLote, '') || COALESCE(' (Fecha lote: '|| to_char(pFechaLote, 
'DD/MM/YYYY')||')', '');

    END IF;    
  END IF;
  
RETURN TRUE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION stock.verificar_lote(integer, integer, integer, character varying, timestamp without time zone, character varying) OWNER TO abacouser;
GRANT EXECUTE ON FUNCTION stock.verificar_lote(integer, integer, integer, character varying, timestamp without time zone, character varying) TO public;
GRANT EXECUTE ON FUNCTION stock.verificar_lote(integer, integer, integer, character varying, timestamp without time zone, character varying) TO abacouser;

-- ============================================

-- DROP FUNCTION public.tg_artm_verificar_lote();

CREATE OR REPLACE FUNCTION public.tg_artm_verificar_lote()
  RETURNS trigger AS
$BODY$
DECLARE
vVerificarXUpd   BOOLEAN;   
BEGIN
 IF (TG_OP = 'UPDATE') THEN
    vVerificarXUpd := (OLD.idart <> NEW.idart) OR (OLD.cant <> NEW.cant) OR 
                      ( COALESCE(OLD.nrolote, '') <> COALESCE(NEW.nrolote, '') ) OR
                      ( COALESCE(OLD.fechalote, now()) <> COALESCE(NEW.fechalote, now()) );
  ELSE
    vVerificarXUpd := FALSE;
  END IF;

  -- verificar baja
  IF ( (TG_OP = 'DELETE') OR vVerificarXUpd ) AND
     (OLD.nrolote IS NOT NULL) AND (OLD.modificaStock = 'S') THEN
    PERFORM stock.verificar_lote(OLD.idcomm, OLD.id, OLD.idart, OLD.nrolote, OLD.fechalote, TG_OP);  
  END IF;

  -- verificar alta
  IF ( (TG_OP = 'INSERT') OR vVerificarXUpd ) AND
     (NEW.nrolote IS NOT NULL) AND (NEW.modificaStock = 'S') THEN
    PERFORM stock.verificar_lote(NEW.idcomm, NEW.id, NEW.idart, NEW.nrolote, NEW.fechalote, TG_OP);
    END IF;

  RETURN NULL; --no necesita devolver la fila porque es un AFTER TRIGGER
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.tg_artm_verificar_lote()
  OWNER TO abacouser;
COMMENT ON FUNCTION public.tg_artm_verificar_lote() IS 'Control sobre la cantidad no negativa del lote';


-- ============================================

-- Trigger: artm_015_ins_upd_del_lote on public.artm

-- DROP TRIGGER artm_015_ins_upd_del_lote ON public.artm;

CREATE TRIGGER artm_015_ins_upd_del_lote
  AFTER INSERT OR UPDATE OR DELETE
  ON public.artm
  FOR EACH ROW
  EXECUTE PROCEDURE public.tg_artm_verificar_lote();


--======================================
SELECT aaVersion('433.03', '433.02');
--======================================
